USE [MusicCatalog]
GO
ALTER TABLE [dbo].[Artists]
	DROP [FoundationDate]
GO

ALTER PROCEDURE [dbo].[AddArtist]
	(
	@ArtistName varchar(50),
	@Description varchar(100),
	@Photo varchar(50),
	@FoundationDate datetime,
	@ArtistID int OUTPUT
	)
AS
SET NOCOUNT ON
	INSERT INTO Artists
	(ArtistName, Description, Photo, FoundationDate)
	VALUES
	(@ArtistName, @Description, @Photo, @FoundationDate)
	SET @ArtistID = scope_identity()
GO

ALTER PROCEDURE [dbo].[UpdateArtist]
	(
	@ArtistID int,
	@ArtistName varchar(50),
	@Description varchar(100),
	@Photo varchar(50),
	@FoundationDate datetime
	)
AS
UPDATE Artists
	SET
	ArtistName = @ArtistName,
	Description =@Description,
	Photo = @Photo,
	FoundationDate = @FoundationDate
	WHERE ArtistID = @ArtistID
GO

ALTER TABLE [dbo].[Albums]
	DROP COLUMN [Url]
GO

ALTER TABLE [dbo].[Albums]
	DROP COLUMN [ReleaseDate]
GO

ALTER TABLE [dbo].[Albums]
	ADD [AlbumYear] [int]
GO

ALTER PROCEDURE [dbo].[UpdateAlbum]
	(
	@AlbumID int,
	@AlbumName varchar(50),
	@AlbumYear int,
	@Style varchar(50),
	@ArtistID int,
	@Cover varchar(50)
	)
AS
UPDATE Albums
	SET
	AlbumName = @AlbumName,
	AlbumYear = @AlbumYear,
	Style = @Style,
	Cover = @Cover,
	ArtistID = @ArtistID
	WHERE AlbumID = @AlbumID
GO

ALTER PROCEDURE [dbo].[AddAlbum]
	(
	@AlbumName varchar(50),
	@AlbumYear int,
	@Style varchar(50),
	@Cover varchar(50),
	@ArtistID int,
	@AlbumID int OUTPUT
	)
AS
SET NOCOUNT ON
	INSERT INTO Albums
	(AlbumName, AlbumYear, Style, Cover, ArtistID)
	VALUES
	(@AlbumName, @AlbumYear, @Style, @Cover, @ArtistID)
	SET @AlbumID = scope_identity()
GO

ALTER PROCEDURE [dbo].[SearchAlbumByName]
	(
	@AlbumName varchar(50)
	)
AS
SET NOCOUNT ON
	SELECT @AlbumName = '%' + RTRIM(@AlbumName) + '%';
	SELECT Albums.AlbumID, Albums.AlbumName, Albums.AlbumYear, Albums.Style,
	Albums.ArtistID, Artists.ArtistName, Albums.Cover
	FROM  Albums LEFT JOIN Artists
	ON Albums.ArtistID = Artists.ArtistID
	WHERE Albums.AlbumName LIKE @AlbumName
GO

ALTER PROCEDURE [dbo].[SearchAlbumById]
	(
	@AlbumID int
	)
AS
SET NOCOUNT ON
	SELECT Albums.AlbumID, Albums.AlbumName, Albums.AlbumYear, Albums.Style,
	Albums.ArtistID, Artists.ArtistName, Albums.Cover
	FROM  Albums LEFT JOIN Artists
	ON Albums.ArtistID = Artists.ArtistID
	WHERE Albums.AlbumID = @AlbumID
GO

ALTER TABLE [dbo].[Tracks]
	DROP COLUMN [TimeTrack]
GO

ALTER PROCEDURE [dbo].[AddTrack]
	(
	@TrackName varchar(50),
	@AlbumID int,
	@Rating int,
	@TrackID int OUTPUT
	)
AS
	SET NOCOUNT ON
	INSERT INTO Tracks
	(TrackName, AlbumID, Rating)
	VALUES
	(@TrackName, @AlbumID, @Rating)
	SET @TrackID = scope_identity()
GO

ALTER PROCEDURE [dbo].[GetTracks]
AS
	SET NOCOUNT ON
	SELECT Tracks.TrackID, Tracks.TrackName, Tracks.AlbumID, Tracks.Rating, Albums.albumName
	FROM Tracks LEFT JOIN Albums
	ON Tracks.AlbumID = Albums.AlbumID ORDER BY Albums.albumName
GO

ALTER PROCEDURE [dbo].[SearchTrackById]
	(
	@TrackID int
	)
AS
	SET NOCOUNT ON
	SELECT Tracks.TrackID, Tracks.TrackName, Tracks.AlbumID, Tracks.Rating, Albums.albumName
	FROM Tracks LEFT JOIN Albums
	ON Tracks.AlbumID = Albums.AlbumID
	WHERE Tracks.TrackID = @TrackID
GO

ALTER PROCEDURE [dbo].[SearchTrackByName]
	(
	@TrackName varchar(50)
	)
AS
	SET NOCOUNT ON
	SELECT @TrackName = '%' + RTRIM(@TrackName) + '%';
	SELECT Tracks.TrackID, Tracks.TrackName, Tracks.AlbumID, Tracks.Rating, Albums.albumName
	FROM Tracks LEFT JOIN Albums
	ON Tracks.AlbumID = Albums.AlbumID
	WHERE Tracks.TrackName LIKE @TrackName
GO

ALTER PROCEDURE [dbo].[UpdateTrack]
	(
	@TrackID int,
	@TrackName varchar(50),
	@Rating int,
	@AlbumID int
	)
AS
	UPDATE Tracks
	SET
	TrackName = @TrackName,
	AlbumID = @AlbumID,
	Rating = @Rating
	WHERE TrackID = @TrackID
GO

ALTER PROCEDURE [dbo].[SearchTracksByAlbum]
	(
	@AlbumID varchar(50)
	)
AS
	SET NOCOUNT ON
	SELECT Tracks.TrackID, Tracks.TrackName, Tracks.AlbumID, Tracks.Rating, Albums.albumName
	FROM Tracks LEFT JOIN Albums
	ON Tracks.AlbumID = Albums.AlbumID
	WHERE Tracks.AlbumID = @AlbumID
GO